package model;

import java.io.*;
import java.util.*;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class InserterComuni {
	static Connection currentCon = null;
	static ResultSet rs = null;

	public static void main(String[] args) {
		try{
			FileInputStream fstream = new FileInputStream("/Users/Francesco/Downloads/listacomuni.txt");
			DataInputStream in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));
			String strLine;
			String insertCityQuery = null;
			String[] regions = new String[20];
			regions[0] = "Veneto";
			regions[1] = "Lombardia";
			regions[2] = "Toscana";
			regions[3] = "Sardegna";
			regions[4] = "Abruzzo";
			regions[5] = "Basilicata";
			regions[6] = "Lazio";
			regions[7] = "Campania";
			regions[8] = "Sicilia";
			regions[9] = "Marche";
			regions[10] = "Calabria";
			regions[11] = "Puglia";
			regions[12] = "Umbria";
			regions[13] = "Molise";
			regions[14] = "Piemonte";
			regions[15] = "Emilia Romagna";
			regions[16] = "Trentino Alto Adige";
			regions[17] = "Friuli Venezia Giulia";
			regions[18] = "Liguria";
			regions[19] = "Valle D Aosta";
			for (int i = 0; i<20; i++)
				insertCityQuery = insertCityQuery + "insert into regions values ('" + regions[i] +"', 'Italy'); ";

			while ((strLine = br.readLine()) != null)   {
				String[] cont = strLine.split(";");
				System.out.println(cont[1]);
				String regCode = cont[3];
				System.out.println(regCode);
				String reg;
				if (regCode.equals("VEN"))
					reg = "Veneto";
				else if (regCode.equals("LOM"))
					reg = "Lombardia";
				else if (regCode.equals("TOS"))
					reg = "Toscana";
				else if (regCode.equals("SAR"))
					reg = "Sardegna";
				else if (regCode.equals("ABR"))
					reg = "Abruzzo";
				else if (regCode.equals("BAS"))
					reg = "Basilicata";
				else if (regCode.equals("LAZ"))
					reg = "Lazio";
				else if (regCode.equals("CAM"))
					reg = "Campania";
				else if (regCode.equals("SIC"))
					reg = "Sicilia";
				else if (regCode.equals("MAR"))
					reg = "Marche";
				else if (regCode.equals("CAL"))
					reg = "Calabria";
				else if (regCode.equals("PUG"))
					reg = "Puglia";
				else if (regCode.equals("UMB"))
					reg = "Umbria";
				else if (regCode.equals("MOL"))
					reg = "Molise";
				else if (regCode.equals("PIE"))
					reg = "Piemonte";
				else if (regCode.equals("EMR"))
					reg = "Emilia Romagna";
				else if (regCode.equals("TAA"))
					reg = "Trentino Alto Adige";
				else if (regCode.equals("FVG"))
					reg = "Friuli Venezia Giulia";
				else if (regCode.equals("LIG"))
					reg = "Liguria";
				else if (regCode.equals("VDA"))
					reg = "Valle D Aosta";
				else
					reg = "ERROR";

				insertCityQuery = insertCityQuery + "insert into city values ('" + cont[1] + "', '" + reg + "', 'Italy'); ";
			}
			try {
				BufferedWriter out = new BufferedWriter(new FileWriter("test.txt"));
				out.write(insertCityQuery);
				out.close();
			} 
			catch (IOException e) 
			{ 
				System.out.println("Exception ");

			}			

			in.close();
		}
		catch (Exception e){
			System.err.println("Error: " + e.getMessage());
		}
	}
}
